﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Automobile.service.Model;
using Automobile.service.Common;
using System.Data;
using System.Data.SqlClient;

namespace Automobile.service.DAL
{
    public class PurchaseDAL
    {
        SQLHelper h = new SQLHelper();
        /// <summary>
        /// 获取商品信息
        /// </summary>
        /// <returns></returns>
        public List<Product> GetProduct()
        {
            string sql = "select Product_Id,Product_Name,Product_Bprid from Product";
            List<Product> list = new List<Product>();
            DataTable data = h.ExecDataTable(sql);
            foreach (DataRow item in data.Rows)
            {
                Product p = new Product();
                p.Product_Id = Convert.ToInt32(item["Product_Id"]);
                p.Product_Name = item["Product_Name"].ToString();
                p.Product_Bprid = Convert.ToDecimal(item["Product_Bprid"]);
                list.Add(p);
            }
            return list;
        }
        /// <summary>
        /// 仓库下拉框
        /// </summary>
        /// <returns></returns>
        public List<WareHose> GetWareHoseList()
        {
            string sql = "select WareHose_Id,WareHose_Name from WareHose";
            List<WareHose> list = new List<WareHose>();
            DataTable data = h.ExecDataTable(sql);
            foreach (DataRow item in data.Rows)
            {
                WareHose w = new WareHose();
                w.WareHose_Id = Convert.ToInt32(item["WareHose_Id"]);
                w.WareHose_Name = item["WareHose_Name"].ToString();
                list.Add(w);
            }
            return list;
        }
        /// <summary>
        /// 供应商下拉框
        /// </summary>
        /// <returns></returns>
        public List<Supplier> GetSupplierList()
        {
            string sql = "select Supplier_Id,Supplier_Name from Supplier";
            List<Supplier> list = new List<Supplier>();
            DataTable data = h.ExecDataTable(sql);
            foreach (DataRow item in data.Rows)
            {
                Supplier s = new Supplier();
                s.Supplier_Id = Convert.ToInt32(item["Supplier_Id"]);
                s.Supplier_Name = item["Supplier_Name"].ToString();
                list.Add(s);
            }
            return list;
        }
        /// <summary>
        /// 经办人下拉框
        /// </summary>
        /// <returns></returns>
        public List<Employee> GetEmployeeList()
        {
            string sql = "select Employee_Id,Employee_Name from Employee";
            List<Employee> list = new List<Employee>();
            DataTable data = h.ExecDataTable(sql);
            foreach (DataRow item in data.Rows)
            {
                Employee e = new Employee();
                e.Employee_Id = Convert.ToInt32(item["Employee_Id"]);
                e.Employee_Name = item["Employee_Name"].ToString();
                list.Add(e);
            }
            return list;
        }
        public List<Product> SearchProductByModelspe(string Product_Modelspe)
        {
            string sql = "select Product_Id,Product_Name,Product_Modelspe,Product_Bprid,Product_Price,Product_Barcode,Product_frim,Product_Unit_Id,Product_Remark,Product_Type_Name,Product.Product_Type_Id from Product join Product_Type on Product.Product_Type_Id=Product_Type.Product_Type_Id where Product_Modelspe like '%" + Product_Modelspe + "%'";
            List<Product> list = new List<Product>();
            DataTable data = h.ExecDataTable(sql);
            foreach (DataRow item in data.Rows)
            {
                Product p = new Product();
                p.Product_Id = Convert.ToInt32(item["Product_Id"]);
                p.Product_Name = item["Product_Name"].ToString();
                p.Product_Modelspe = item["Product_Modelspe"].ToString();
                p.Product_Price = Convert.ToDecimal(item["Product_Price"]);
                p.Product_Bprid = Convert.ToDecimal(item["Product_Bprid"]);
                p.Product_Barcode = item["Product_Barcode"].ToString();
                p.Product_frim = item["Product_frim"].ToString();
                p.Product_Unit_Id = item["Product_Unit_Id"].ToString();
                p.Product_Remark = item["Product_Remark"].ToString();
                p.Product_Type_Name = item["Product_Type_Name"].ToString();
                p.Product_Type_Id = Convert.ToInt32(item["Product_Type_Id"]);

                list.Add(p);
            }
            return list;
        }
        /// <summary>
        /// 添加采购入库单信息
        /// </summary>
        /// <param name="P"></param>
        /// <param name="xml"></param>
        /// <returns></returns>
        public int Purchase_IntoList(Purchase P, string xml)
        {
            string str = "Purchase_IntoList";
            SqlParameter[] par = new SqlParameter[] { 
           
                       new SqlParameter("@Purchase_State",P.Purchase_State),
                       new SqlParameter("@SumMoney",P.SumMoney),
                       new SqlParameter("@Supplier_Id",P.Supplier_Id),
                       new SqlParameter("@WareHose_Id",P.WareHose_Id),
                       new SqlParameter("@Employee_Id",P.Employee_Id),
                       new SqlParameter("@PurChase_InOut",P.PurChase_InOut),
                       new SqlParameter("@Purchase_curom",P.Purchase_curom),
                       new SqlParameter("@xml",xml),
           
           };
            return h.ExecNonquery_Proc(str, par);
        }
        /// <summary>
        /// 显示采购主表
        /// </summary>
        /// <returns></returns>
        public List<Purchase> PurIntoShow()
        {
            string sql = "select Purchase_Id,Purchase_curom,Purchase_date,Purchase_State,SumMoney,p.Supplier_Id,p.WareHose_Id,p.Employee_Id,Supplier_Name,WareHose_Name,Employee_Name from Purchase p join Supplier s on p.Supplier_Id=s.Supplier_Id join WareHose w on p.WareHose_Id=w.WareHose_Id join Employee e on p.Employee_Id=e.Employee_Id where PurChase_InOut='采购' order by Purchase_date DESC ";
            List<Purchase> list = new List<Purchase>();
            DataTable data = h.ExecDataTable(sql);
            foreach (DataRow item in data.Rows)
            {
                Purchase p = new Purchase();
                p.Purchase_Id = Convert.ToInt32(item["Purchase_Id"]);
                p.Purchase_curom = item["Purchase_curom"].ToString();
                p.Purchase_date = Convert.ToDateTime(item["Purchase_date"]);
                p.Purchase_State = Convert.ToInt32(item["Purchase_State"]);
                p.SumMoney = Convert.ToInt32(item["SumMoney"]);
                p.Supplier_Id = Convert.ToInt32(item["Supplier_Id"]);
                p.WareHose_Id = Convert.ToInt32(item["WareHose_Id"]);
                p.Employee_Id = Convert.ToInt32(item["Employee_Id"]);
                p.Supplier_Name = item["Supplier_Name"].ToString();
                p.WareHose_Name = item["WareHose_Name"].ToString();
                p.Employee_Name = item["Employee_Name"].ToString();

                list.Add(p);
            }
            return list;
        }
        /// <summary>
        /// 审核页面的主表显示
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public List<Purchase> PurCheckMain(int id)
        {
            string sql = "select Purchase_Id,Purchase_curom,Purchase_date,Purchase_State,SumMoney,p.Supplier_Id,p.WareHose_Id,p.Employee_Id,Supplier_Name,WareHose_Name,Employee_Name from Purchase p join Supplier s on p.Supplier_Id=s.Supplier_Id join WareHose w on p.WareHose_Id=w.WareHose_Id join Employee e on p.Employee_Id=e.Employee_Id where Purchase_Id =" + id + "";
            List<Purchase> list = new List<Purchase>();
            DataTable data = h.ExecDataTable(sql);
            foreach (DataRow item in data.Rows)
            {
                Purchase p = new Purchase();
                p.Purchase_Id = Convert.ToInt32(item["Purchase_Id"]);
                p.Purchase_curom = item["Purchase_curom"].ToString();
                p.Purchase_date = Convert.ToDateTime(item["Purchase_date"]);
                p.Purchase_State = Convert.ToInt32(item["Purchase_State"]);
                p.SumMoney = Convert.ToInt32(item["SumMoney"]);
                p.Supplier_Id = Convert.ToInt32(item["Supplier_Id"]);
                p.WareHose_Id = Convert.ToInt32(item["WareHose_Id"]);
                p.Employee_Id = Convert.ToInt32(item["Employee_Id"]);
                p.Supplier_Name = item["Supplier_Name"].ToString();
                p.WareHose_Name = item["WareHose_Name"].ToString();
                p.Employee_Name = item["Employee_Name"].ToString();

                list.Add(p);
            }
            return list;
        }
        /// <summary>
        /// 审核页面的子表显示
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public List<PurchaseMate> PurCheckMate(int id)
        {
            string sql = "select PurchaseMate_Id,Purchase_Id,purchasemate.Product_Id,PurchaseMate_Num,Product_Name,PurchaseMate_Price from PurchaseMate join Product on PurchaseMate.Product_Id=Product.Product_Id where Purchase_Id=" + id + "";
            List<PurchaseMate> list = new List<PurchaseMate>();
            DataTable data = h.ExecDataTable(sql);
            foreach (DataRow item in data.Rows)
            {
                PurchaseMate p = new PurchaseMate();
                p.PurchaseMate_Id = Convert.ToInt32(item["PurchaseMate_Id"]);
                p.Purchase_Id = Convert.ToInt32(item["Purchase_Id"]);
                p.Product_Name = item["Product_Name"].ToString();
                p.PurchaseMate_Num = Convert.ToInt32(item["PurchaseMate_Num"]);
                p.PurchaseMate_Price = Convert.ToInt32(item["PurchaseMate_Price"]);
                p.Product_Id = Convert.ToInt32(item["Product_Id"]);

                list.Add(p);
            }
            return list;

        }
        /// <summary>
        /// 入库操作
        /// </summary>
        /// <param name="Purchase_Id"></param>
        /// <returns></returns>
        public int IntoStock(int Purchase_Id)
        {
            string procname = "Proc_IntoWarehouse";
            SqlParameter[] par = new SqlParameter[]{
              new SqlParameter("@Purchase_Id",Purchase_Id)
          };
            return h.ExecNonquery_Proc(procname, par);

        }
    }
}
